IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('PaintAuthorshipBatchUpdate','P'))
    DROP PROCEDURE PaintAuthorshipBatchUpdate
GO
/*
 * Designer:     Kevin
 * Description:    
 * Created:      03/10/2011
 * History:
 * =============================================================================
 * Author      DateTime        Alter Description
 * =============================================================================
 */

CREATE PROCEDURE PaintAuthorshipBatchUpdate
(
	@PaintingId int,
	@Authorships nvarchar(max)
)
AS
BEGIN

	if @Authorships is null or @Authorships=''
	begin
		return
	end
	declare @TempAuthorship table(
		AuthorshipId int,
		AuthorshipValue nvarchar(100)
	)

	insert into @TempAuthorship(
		AuthorshipValue
	)
	select Item as AuthorshipValue
	from FunStringSplit(@Authorships, ',')
	
	insert into Authorship(
		[value]
	)
	select AuthorshipValue
	from @TempAuthorship TempAuthorship
	left join Authorship on TempAuthorship.AuthorshipValue=Authorship.[value]
	where Authorship.[value] is null
	
	update TempAuthorship
	set 
		AuthorshipId=Authorship.Authorship_id
	from 
		@TempAuthorship TempAuthorship
		inner join Authorship on TempAuthorship.AuthorshipValue=Authorship.[value]
		
	update Authorship
	set count_of_paint=count_of_paint+1
	from @TempAuthorship TempAuthorship
	left join painting_Authorship on TempAuthorship.AuthorshipId=painting_Authorship.Authorship_id
	where painting_Authorship.Authorship_id is null
	
	insert into painting_Authorship (
		painting_id,
		Authorship_id
	)
	select 
		@PaintingId,
		TempAuthorship.AuthorshipId
	from @TempAuthorship TempAuthorship
	left join painting_Authorship on TempAuthorship.AuthorshipId=painting_Authorship.Authorship_id
	where painting_Authorship.Authorship_id is null
	
	update Authorship
	set count_of_paint=count_of_paint-1
	from painting_Authorship 
	left join @TempAuthorship TempAuthorship on TempAuthorship.AuthorshipId=painting_Authorship.Authorship_id
	where TempAuthorship.AuthorshipId is null
	
	delete from painting_Authorship
	from painting_Authorship
	left join @TempAuthorship TempAuthorship on TempAuthorship.AuthorshipId=painting_Authorship.Authorship_id
	where TempAuthorship.AuthorshipId is null		
END
GO